package fisio.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import fisio.connection.ConnectionManager;
import fisio.global.Util;
import fisio.model.Avaliacao;
import fisio.model.Endereco;
import fisio.model.Foto;
import fisio.model.Paciente;

public class PacienteDAO {

	private static final String SQL_UPDATE_PACIENTE = "UPDATE paciente SET nome = ?, sexo = ?, data_nasc = ?, profissao = ?, fone1 = ?, fone2 = ?, email = ?, login = ?, senha = ?, obs = ?, id_fo = ? WHERE id_p = ?";
	private static final String SQL_UPDATE_ENDERECO = "UPDATE endereco SET logradouro = ?, numero = ?, complemento = ?, bairro = ?, cidade = ?, cep = ? WHERE id_p = ?";
	private static final String SQL_INSERT_PACIENTE = "INSERT INTO paciente (id_fo, nome, sexo, data_nasc, profissao, fone1, fone2, email, login, senha, obs) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String SQL_INSERT_ENDERECO = "INSERT INTO endereco (id_p, logradouro, numero, complemento, bairro, cidade, cep) VALUES (?, ?, ?, ?, ?, ?, ?)";
	private static final String SQL_INSERT_AVALIACAO = "INSERT INTO avaliacao (id_p, data_reg, altura, peso) VALUES (?, ?, ?, ?)";
	private static final String SQL_DELETE_PACIENTE = "DELETE FROM paciente WHERE id_p = ?";
	private static final String SQL_GET_PACIENTE_BY_ID = "SELECT * FROM paciente JOIN endereco USING(id_p) WHERE id_p = ?";
	private static final String SQL_GET_AVALIACAO_BY_ID = "SELECT data_reg, altura, peso FROM avaliacao WHERE id_p = ? ORDER BY data_reg DESC";
	private static final String SQL_GET_PACIENTES = "SELECT id_p, nome, sexo, data_nasc, email FROM paciente ORDER BY nome ASC";
	private static final String SQL_GET_PACIENTES_COMPLETOS = "SELECT * FROM paciente JOIN endereco USING(id_p) ORDER BY nome ASC";
	
	public static Paciente getPaciente(int id) {

		Connection conn = ConnectionManager.getConnection();
		Paciente paciente = null;

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_GET_PACIENTE_BY_ID);
			stmt.setInt(1, id);
			
			ResultSet rs = stmt.executeQuery();
			if(rs.next()) {
				paciente = new Paciente(
								rs.getInt(1),
								rs.getString(3),
								rs.getInt(4)==1?true:false,
								Util.convertDateSqlToUtil(rs.getDate(5)),
								rs.getString(6),
								rs.getString(7),
								rs.getString(8),
								rs.getString(9),
								rs.getString(10),
								rs.getString(11),
								rs.getString(12),
								null,
								new Endereco(rs.getString(13),rs.getInt(14),rs.getString(15),rs.getString(16),rs.getString(17),rs.getString(18)),
								new Vector<Avaliacao>()
						);
				
				paciente.setFoto(FotoDAO.getFoto(rs.getInt(2)));
				
				stmt = conn.prepareStatement(SQL_GET_AVALIACAO_BY_ID);
				stmt.setInt(1, id);
				rs = stmt.executeQuery();
				while(rs.next())
					paciente.getAvaliacoes().add(new Avaliacao(Util.convertDateSqlToUtil(rs.getDate(1)), rs.getInt(2), rs.getInt(3)));
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}

		return paciente;
	}
	
	public static Vector<Paciente> getPacientes() {

		Connection conn = ConnectionManager.getConnection();
		Vector<Paciente> pacientes = new Vector<Paciente>();

		try {
			PreparedStatement stmt = conn.prepareStatement(SQL_GET_PACIENTES);
			
			ResultSet rs = stmt.executeQuery();
			Paciente paciente;
			while(rs.next()) {
				
				paciente = new Paciente(
						rs.getInt(1),
						rs.getString(2),
						rs.getInt(3)==1?true:false,
						Util.convertDateSqlToUtil(rs.getDate(4)),
						rs.getString(5)
				);

				pacientes.add(paciente);
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}

		return pacientes;
	}
	
	public static Vector<Paciente> getPacientesCompletos() {

		Connection conn = ConnectionManager.getConnection();
		Vector<Paciente> pacientes = new Vector<Paciente>();

		try {
			PreparedStatement stmt = conn.prepareStatement(SQL_GET_PACIENTES_COMPLETOS);
			
			ResultSet rs = stmt.executeQuery();
			Paciente paciente;
			while(rs.next()) {
				
				paciente = new Paciente(
						rs.getInt(1),
						rs.getString(3),
						rs.getInt(4)==1?true:false,
						Util.convertDateSqlToUtil(rs.getDate(5)),
						rs.getString(6),
						rs.getString(7),
						rs.getString(8),
						rs.getString(9),
						rs.getString(10),
						rs.getString(11),
						rs.getString(12),
						null,
						new Endereco(rs.getString(13),rs.getInt(14),rs.getString(15),rs.getString(16),rs.getString(17),rs.getString(18)),
						new Vector<Avaliacao>()
				);

				paciente.setFoto(FotoDAO.getFoto(rs.getInt(2)));
		
				PreparedStatement stmt2 = conn.prepareStatement(SQL_GET_AVALIACAO_BY_ID);
				stmt2.setInt(1, paciente.getId());
				ResultSet rs2 = stmt2.executeQuery();
				while(rs2.next())
					paciente.getAvaliacoes().add(new Avaliacao(Util.convertDateSqlToUtil(rs2.getDate(1)), rs2.getInt(2), rs2.getInt(3)));
				
				pacientes.add(paciente);
				rs2.close();
				stmt2.close();
			}

			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na consulta: "+e.getMessage());
		}

		return pacientes;
	}
	
	public static Paciente insertPaciente(Paciente paciente) {
		
		Connection conn = ConnectionManager.getConnection();

		int id_p = -1;
		try {
			
			PreparedStatement stmt = conn.prepareStatement(SQL_INSERT_PACIENTE, Statement.RETURN_GENERATED_KEYS);
			stmt.setNull(1, java.sql.Types.INTEGER);
			stmt.setString(2, paciente.getNome());
			stmt.setInt(3, paciente.getSexo()?1:0);
			stmt.setDate(4, Util.convertDateUtilToSql(paciente.getDataNasc()));
			stmt.setString(5, paciente.getProfissao());
			stmt.setString(6, paciente.getFone1());
			stmt.setString(7, paciente.getFone2());
			stmt.setString(8, paciente.getEmail());
			stmt.setString(9, paciente.getLogin());
			stmt.setString(10, paciente.getSenha());
			stmt.setString(11, paciente.getObs());
			
			stmt.executeUpdate();
			
			ResultSet rs = stmt.getGeneratedKeys();
			if(rs.next()){
				id_p = rs.getInt(1);
				
				stmt = conn.prepareStatement(SQL_INSERT_ENDERECO);
				stmt.setInt(1, id_p);
				stmt.setString(2, paciente.getEndereco().getLogradouro());
				stmt.setInt(3, paciente.getEndereco().getNumero());
				stmt.setString(4, paciente.getEndereco().getComplemento());
				stmt.setString(5, paciente.getEndereco().getBairro());
				stmt.setString(6, paciente.getEndereco().getCidade());
				stmt.setString(7, Util.unmaskCEP(paciente.getEndereco().getCEP()));
				stmt.executeUpdate();
				
				for(int i = 0; i < paciente.getAvaliacoes().size(); i++){
					stmt = conn.prepareStatement(SQL_INSERT_AVALIACAO);
					stmt.setInt(1, id_p);
					stmt.setDate(2, Util.convertDateUtilToSql(paciente.getAvaliacoes().get(i).getDataReg()));
					stmt.setInt(3, paciente.getAvaliacoes().get(i).getAltura());
					stmt.setInt(4, paciente.getAvaliacoes().get(i).getPeso());
					stmt.executeUpdate();
				}
				
			}
			rs.close();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na insercao: "+e.getMessage());
		}
		paciente.setId(id_p);
		return paciente;
	}

	public static void deletePaciente(Paciente paciente) {
		Connection conn = ConnectionManager.getConnection();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_DELETE_PACIENTE);
			stmt.setInt(1, paciente.getId());
			stmt.executeUpdate();
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na remocao: "+e.getMessage());
		}
	}

	public static void editPaciente(Paciente paciente) {
		Connection conn = ConnectionManager.getConnection();

		try {

			PreparedStatement stmt = conn.prepareStatement(SQL_UPDATE_PACIENTE);
			stmt.setString(1, paciente.getNome());
			stmt.setBoolean(2, paciente.getSexo());
			stmt.setDate(3, Util.convertDateUtilToSql(paciente.getDataNasc()));
			stmt.setString(4, paciente.getProfissao());
			stmt.setString(5, paciente.getFone1());
			stmt.setString(6, paciente.getFone2());
			stmt.setString(7, paciente.getEmail());
			stmt.setString(8, paciente.getLogin());
			stmt.setString(9, paciente.getSenha());
			stmt.setString(10, paciente.getObs());
			if (paciente.getFoto() == null)
				stmt.setNull(11, java.sql.Types.INTEGER);
			else
				stmt.setInt(11, paciente.getFoto().getId());
			stmt.setInt(12, paciente.getId());
			stmt.executeUpdate();
			
			stmt = conn.prepareStatement(SQL_UPDATE_ENDERECO);
			stmt.setString(1, paciente.getEndereco().getLogradouro());
			stmt.setInt(2, paciente.getEndereco().getNumero());
			stmt.setString(3, paciente.getEndereco().getComplemento());
			stmt.setString(4, paciente.getEndereco().getBairro());
			stmt.setString(5, paciente.getEndereco().getCidade());
			stmt.setString(6, paciente.getEndereco().getCEP());
			stmt.setInt(7, paciente.getId());
			stmt.executeUpdate();
			
			if(paciente.getAvaliacoes().get(0).getAltura() > 0 && paciente.getAvaliacoes().get(0).getPeso() > 0){
				stmt = conn.prepareStatement(SQL_INSERT_AVALIACAO);
				stmt.setInt(1, paciente.getId());
				stmt.setDate(2, Util.convertDateUtilToSql(paciente.getAvaliacoes().get(0).getDataReg()));
				stmt.setInt(3, paciente.getAvaliacoes().get(0).getAltura());
				stmt.setInt(4, paciente.getAvaliacoes().get(0).getPeso());
				stmt.executeUpdate();
			}
			
			stmt.close();
			conn.close();

		} catch(SQLException e) {
			System.out.println("\nERRO na edicao: "+e.getMessage());
		}
	}
}